package dao;

import bean.User;
import java.sql.*;
import java.util.List;
import java.util.Objects;
import java.util.Vector;

import org.eclipse.jdt.annotation.NonNull;

public class UserDAOImpl implements UserDAO {
    private Connection conn;
    private static final String INSERT_STATEMENT = "INSERT INTO USERS VALUES(NULL,?,?,?,?)";
    private static final String UPDATE_STATEMENT = "UPDATE USERS SET USER_NAME=?,SEX=?,BIRTHDATE=?,ADDRESS=? WHERE USER_ID=?";
    private static final String SELECT_COUNT_STATEMENT = "SELECT COUNT(*) FROM USERS";
    private static final String SELECT_STATEMENT = "SELECT * FROM USERS";
    private static final String SELECT_BY_ID_STATEMENT = "SELECT * FROM USERS WHERE USER_ID=?";
    private static final String DELETE_STATEMENT = "DELETE FROM USERS WHERE USER_ID=?";

    public UserDAOImpl(@NonNull Connection conn) {
        this.conn = conn;
    }
    
    @Override
    public PreparedStatement verifyUser(@NonNull PreparedStatement ps, @NonNull User user) throws SQLException {
        String userName = user.getUserName();
        if (User.verifyUserName(userName)) {
            ps.setString(1, userName);
            ps.setInt(2, user.getSex());
            Date birthdate = user.getBirthdate();
            if (Objects.nonNull(birthdate)) {
                ps.setDate(3, birthdate);
            } else {
                ps.setNull(3, Types.DATE);
            }
            String address = user.getAddress();
            if (User.verifyAddress(address)) {
                ps.setString(4, address);
            } else {
                ps.setNull(4, Types.VARCHAR);
            }
            return ps;
        }
        return null;
    }

    @Override
    public boolean insert(@NonNull User user) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(INSERT_STATEMENT);
        ps = verifyUser(ps, user);
        if (Objects.nonNull(ps)) {
            boolean state = ps.executeUpdate() == 1;
            ps.close();
            return state;
        }
        return false;
    }

    @Override
    public boolean update(@NonNull User user) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(UPDATE_STATEMENT);
        ps = verifyUser(ps, user);
        if (Objects.nonNull(ps)) {
            ps.setInt(5, user.getUserId());
            boolean state = ps.executeUpdate() == 1;
            ps.close();
            return state;
        }
        return false;
    }
    
    @Override
    public List<User> selectAll() throws SQLException {
        List<User> users = null;
        ResultSet rs = conn.createStatement().executeQuery(SELECT_COUNT_STATEMENT);
        if (rs.next()) {
            users = new Vector<>(rs.getInt(1));
            rs.close();
            PreparedStatement ps = conn.prepareStatement(SELECT_STATEMENT);
            rs = ps.executeQuery();
            while (rs.next()) {
                int userId = rs.getInt("USER_ID");
                String userName = rs.getString("USER_NAME");
                int sex = rs.getInt("SEX");
                Date birthdate = rs.getDate("BIRTHDATE");
                String address = rs.getString("ADDRESS");
                User user = new User(userId, userName, sex, birthdate, address);
                users.add(user);
            }
            ps.close();
        }
        rs.close();
        return users;
    }

    @Override
    public User select(int userId) throws SQLException {
        User user = null;
        PreparedStatement ps = conn.prepareStatement(SELECT_BY_ID_STATEMENT);
        ps.setInt(1, userId);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            String userName = rs.getString("USER_NAME");
            int sex = rs.getInt("SEX");
            Date birthdate = rs.getDate("BIRTHDATE");
            String address = rs.getString("ADDRESS");
            user = new User(userId, userName, sex, birthdate, address);
        }
        rs.close();
        ps.close();
        return user;
    }

    @Override
    public boolean delete(int userId) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(DELETE_STATEMENT);
        ps.setInt(1, userId);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }
}
